INSERT OVERWRITE TABLE jms_dm.dm_agg_collect_aging_by_plan_dt PARTITION (dt='{{ execution_date | date_add(-1) | cst_ds }}')
SELECT taking_network_id                                    AS taking_network_id,             -- 收件网点ID
       max(taking_network_code)                             AS taking_network_code,           -- 收件网点编码
       max(taking_network_name)                             AS taking_network_name,           -- 收件网点名称
       entrepot_network_id                                  AS entrepot_network_id,           -- 交件集散点ID
       max(entrepot_network_code)                           AS entrepot_network_code,         -- 交件集散点编码
       max(entrepot_network_name)                           AS entrepot_network_name,         -- 交件集散点名称
       center_id                                            AS center_id,                     -- 交件中心ID
       max(center_code)                                     AS center_code,                   -- 交件中心编码
       max(center_name)                                     AS center_name,                   -- 交件中心名称
       max(goods_type_id)                                   AS goods_type_id,                 -- 物品类型ID
       goods_type_code                                      AS goods_type_code,               -- 物品类型code
       max(goods_type_name)                                 AS goods_type_name,               -- 物品类型名称
       order_source_code                                    AS order_source_code,             -- 渠道code
       max(order_source_name)                               AS order_source_name,             -- 渠道名称
       sub_source_code                                      AS sub_source_code,               -- 渠道细分code
       max(taking_regional_id)                              AS taking_regional_id,            -- 收件大区ID
       max(taking_regional_desc)                            AS taking_regional_desc,          -- 收件大区名称
       max(taking_provider_id)                              AS taking_provider_id,            -- 收件省份ID
       max(taking_provider_desc)                            AS taking_provider_desc,          -- 收件省份名称
       max(taking_city_id)                                  AS taking_city_id,                -- 收件城市ID
       max(taking_city_desc)                                AS taking_city_desc,              -- 收件城市名称
       max(taking_agent_id)                                 AS taking_agent_id,               -- 收件代理区ID
       max(taking_agent_code)                               AS taking_agent_code,             -- 收件代理区编码
       max(taking_agent_name)                               AS taking_agent_name,             -- 收件代理区名称
       max(taking_first_franchisee_id)                      AS taking_first_franchisee_id,    -- 收件一级加盟商ID
       max(taking_first_franchisee_code)                    AS taking_first_franchisee_code,  -- 收件一级加盟商编码
       max(taking_first_franchisee_name)                    AS taking_first_franchisee_name,  -- 收件一级加盟商名称
       max(taking_second_franchisee_id)                     AS taking_second_franchisee_id,   -- 收件二级加盟商ID
       max(taking_second_franchisee_code)                   AS taking_second_franchisee_code, -- 收件二级加盟商编码
       max(taking_second_franchisee_name)                   AS taking_second_franchisee_name, -- 收件二级加盟商名称
       sum(1)                                               AS full_cnt,                      -- 总票数
       nvl(sum(is_center_in_time), 0)                       AS center_in_time_cnt,            -- 到达中心准点票数
       nvl(sum(is_entrepot_in_time), 0)                     AS entrepot_in_time_cnt,          -- 到达集散准点票数
       sum(if(duty_network_id = taking_network_id, 1, 0))   AS network_duty_cnt,              -- 网点责任票数
       sum(if(duty_network_id = entrepot_network_id, 1, 0)) AS entrepot_duty_cnt              -- 集散责任票数
  FROM jms_dm.dm_waybill_collect_aging_dt
 WHERE dt BETWEEN date_add('{{ execution_date | cst_ds }}', -3) AND date_add('{{ execution_date | cst_ds }}', -1)
   AND taking_network_id IS NOT NULL
   AND date(center_planned_arrival_time) = date_add('{{ execution_date | cst_ds }}', -1)
 GROUP BY taking_network_id, entrepot_network_id, center_id, goods_type_code, order_source_code, sub_source_code
DISTRIBUTE BY pmod(hash(taking_network_id), 4);
